package com.tinyideatech.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tinyideatech.system.entity.CarOrder;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @author zhangy-5
 * @date 2019-09-09
 */
@Repository
public interface CarOrderMapper extends BaseMapper<CarOrder> {
    @Update("UPDATE car_order SET id = #{id},prepay_id=#{prepayId},time_end=#{timeEnd},update_time=#{updateTime},feature=#{feature} where device_id=#{deivceId}")
    int updateOrderByDeviceName(String id, String prepayId, Date timeEnd, Date updateTime, int feature, String deivceId);

    @Select("select * from car_order where date_sub(curdate(), INTERVAL 7 DAY) <= date(`update_time`);")
    List<CarOrder> selectListByWeek();

    @Select("SELECT SUM(total_fee) as total FROM car_order WHERE transaction_id IS NOT NULL ")
    Integer consume();

    @Select("SELECT SUM(total_fee) as total FROM car_order WHERE transaction_id IS NOT NULL and YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1);")
    Integer sumByMonth(Date date);

    @Select("SELECT a.num1 - b.num2 as plus from\n" +
            "(SELECT COUNT(id) as num1 from car_order where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1))a,\n" +
            "((SELECT COUNT(id) as num2 from car_order where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1)-1))b")
    int singlePlus(Date date);

    // @Select("SELECT COUNT(device_id) as num from car_order where DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')")
    @Select("select count(device_id) as num from car_order where year(create_time)=#{year} and MONTH(create_time)=#{month}")
    int countByMonth(int year, int month);


    @Select("select COUNT(id) as num from car_order where TO_DAYS(create_time) = TO_DAYS(#{date})")
    int countByToday(Date date);
}